package MTT::Libs::Search;

use Moose;


use utf8;

use Data::Dumper;
use IO::File;
use POSIX qw(strftime);
use Class::Date qw(date);
use HTML::Table;
use Spreadsheet::WriteExcel;


has dbh => ( is => 'ro', required => 1 );
has data => ( is => 'ro', writer => '_set_data' );
has ctx => ( is => 'rw' );
has report_writer => ( is => 'rw', default => sub { new Search::Writer::HTML }, lazy => 1 );

has format => ( is => 'rw' );

has tables => ( is => 'ro', isa => 'HashRef', default => sub { {} } );

has headers => ( is => 'ro', isa => 'HashRef', default => sub { {} } );

has success => ( is => 'rw', isa => 'Bool', default => 0 );

has file 	=> ( 
	is => 'ro', 
	default => sub { ( $^O =~ /MSWin/i ? 'd:/tmp/sr_' : '/tmp/sr_' )  . int( rand 1000 ) }, lazy => 1 );

has result_rows => ( is => 'rw' );

has 'log' => ( is => 'ro' );

sub get_header {
	my ($self, $table) = @_;

	my ($t_type) = $table =~ /^([^_]+)/;
	return [] unless $self->tables->{$t_type};

	if ( $self->ctx->req->params->{viewtype} eq 'short' ) {
		if ( $table =~ /^acme/ ) {
			return [ ( ( $self->format eq 'html' ? ( 'Трассировка' ) : () ), 'Протокол', 'Switch', 'NAS IP', 'Начало вызова', 'Окончание вызова', 
						'trunk IN', 'trunk OUT', 'А номер', 'Б номер', 'Длительность вызова, сек.',
						'Длительность разговора, сек.', 'Disc. init.', 'Релиз', 'Б код') ] ;
		}
		else {
			return [ ('Протокол', 'Switch', 'Начало вызова', 'Окончание вызова', 
						'Вх.', 'Исх.', 'А номер', 'Б номер', 'Длительность вызова',
						'Длительность разговора', 'Релиз', 'Б код') ] ;
		}
	}

	unless ( @{ $self->tables->{$t_type} } ) {

		my $columns = $self->dbh->selectall_arrayref(			
					"SELECT column_name FROM information_schema.columns WHERE table_schema='calls' AND table_name = '$table' ORDER BY ordinal_position");

		foreach ( @$columns ) {
			my $hd = $self->ctx->model('DBIC::FieldName')->single({type => $t_type, field => $_});
			push @{ $self->tables->{$t_type} }, ( $hd ? $hd->name : $_->[0] );
		}
	}
	return $self->tables->{$t_type};
}

sub search {
	my ($self, $c) = @_;

	$c->user_log->info('поиск', 'search');

	$self->ctx( $c );


	$self->format( $self->ctx->req->params->{format} ) 
		if $self->ctx->req->params->{format};
	$self->format('html') unless $self->format;


	unless ( $self->format eq 'html' ) {
		if ( $self->format eq 'csv' ) {
			$self->report_writer( new Search::Writer::CSV file => $self->file ); 
		}
		elsif ( $self->format eq 'excel' ) {
			$self->report_writer( new Search::Writer::Excel file => $self->file );
		}
	}

	foreach ( $c->model('DBIC::SwitchType')->all() ) {
		$self->tables->{ $_->table_name } = [];
	}

	my $header;

	foreach ( $c->model('DBIC::FieldName')->all() ) {
		$self->headers->{ $_->type }->{ $_->field } = $_->name;
	}

	$self->get_data($c);
}


sub get_data {
	my ($self, $c) = @_;

	my $start_time = $c->req->params->{start_time} || strftime('%Y-%m-%d 00:00:00', localtime);
	my $end_time   = $c->req->params->{end_time} || strftime('%Y-%m-%d %H:%M:%S', localtime);
	my $dnow = strftime('%Y-%m-%d', localtime);

	my $switches = $c->req->params->{switches_do};
	$switches = [ $switches ] if $switches && !ref $switches;
	my @switches;
	if ( $switches ) {
		foreach ( $c->model('DBIC::Switch')->search({id => {in => $switches}}) ) {
			push @switches, $_->host_code;
		}
	}

	my $trunks_in = $c->req->params->{trunks_in};
	my $trunks_out = $c->req->params->{trunks_out};
	$trunks_in = [ $trunks_in ] if $trunks_in && !ref $trunks_in;
	$trunks_out = [ $trunks_out ] if $trunks_out && !ref $trunks_out;
	my %ts = map { $_ => 1 } @$trunks_in;
	@$trunks_in = keys %ts;
	%ts = map { $_ => 1 } @$trunks_out;
	@$trunks_out = keys %ts;

	my $trunks_or = $c->req->params->{trunk_cond};	

	my $operators_in = $c->req->params->{operators_in};
	my $operators_out = $c->req->params->{operators_out};
	$operators_in = [ $operators_in ] if $operators_in && !ref $operators_in;
	$operators_out = [ $operators_out ] if $operators_out && !ref $operators_out;

	my $ops_or = $c->req->params->{operator_cond};	

	my (@op_trunks_in, @op_trunks_out);
	if ( $operators_in ) {
		foreach ( $c->model('DBIC::Trunk')->search({company_name => { in => $operators_in }, created => $dnow}) ) {
			push @op_trunks_in, $_->trunk;
		}
	}

	if ( $operators_out ) {
		foreach ( $c->model('DBIC::Trunk')->search({company_name => { in => $operators_out }, created => $dnow}) ) {
			push @op_trunks_out, $_->trunk;
		}
	}

	%ts = map { $_ => 1 } @op_trunks_in;
	@op_trunks_in = keys %ts;
	%ts = map { $_ => 1 } @op_trunks_out;
	@op_trunks_out = keys %ts;


	my %table_names;

	if ( $switches ) {
		foreach ( $c->model('DBIC::Switch')->search({
					host_code => { in => $switches } }) ) 
		{
			$table_names{ $_->id_type->table_name } = 1;
		}
	}

	if ( $trunks_in || $trunks_out || @op_trunks_out || @op_trunks_in ) {
		my @ts;
		push @ts, @$trunks_in if $trunks_in;
		push @ts, @$trunks_out if $trunks_out;
		push @ts, @op_trunks_in if @op_trunks_in;
		push @ts, @op_trunks_out if @op_trunks_out;
		my @sw;
		foreach ( $c->model('DBIC::Trunk')->search({trunk => {in => \@ts}, created => $dnow}) ) {
			push @sw, $_->switch;						
		}

		foreach ( $c->model('DBIC::Switch')->search({host_code => {in => \@sw}}) ) {
			$table_names{ $_->id_type->table_name } = 1;
		}
	}

	my @tn = keys %table_names;
	my %tables = %{ $self->get_tables( $start_time, $end_time, \@tn ) };


	my $where = '';
	if ( $switches ) {
		$where .= ' AND switch IN (' . join(', ', map { "'$_'" } @$switches) . ')';
	}

	if ( @$trunks_in && @$trunks_out && $trunks_or ) {
		$where .= ' AND (trunk_in IN (' . join(', ', map { qq{'$_'} } @$trunks_in) . ')'
				. ' OR trunk_out IN (' . join(', ', map { qq{'$_'} } @$trunks_out) . '))'
	}
	else {
		if ( @$trunks_in ) {
			$where .= ' AND trunk_in IN (' . join(', ', map { "'$_'" } @$trunks_in) . ')';
		}
		if ( @$trunks_out ) {
			$where .= ' AND trunk_out IN (' . join(', ', map { "'$_'" } @$trunks_out) . ')';
		}	
	}

	if ( @op_trunks_in && @op_trunks_out && $ops_or ) {
		$where .= ' AND (trunk_in IN (' . join(', ', map { qq{'$_'} } @op_trunks_in) . ')'
				. ' OR trunk_out IN (' . join(', ', map { qq{'$_'} } @op_trunks_out) . '))'
	}
	else {
		if ( @op_trunks_in ) {
			$where .= ' AND trunk_in IN (' . join(', ', map { "'$_'" } @op_trunks_in) . ')';
		}
		if ( @op_trunks_out ) {
			$where .= ' AND trunk_out IN (' . join(', ', map { "'$_'" } @op_trunks_out) . ')';
		}	
	}

	if ( $c->req->params->{anumber} && 
		 $c->req->params->{bnumber} &&
		 $c->req->params->{mask_cond} )
	{
		$where .= " AND (a_number LIKE '" . $c->req->params->{anumber} . "\%'"
				. " OR   b_number LIKE '" . $c->req->params->{bnumber} . "\%')";
	}
	else {
		if ( $c->req->params->{anumber} ) {
			$where .= " AND a_number LIKE '" . $c->req->params->{anumber} . "\%'";
		}

		if ( $c->req->params->{bnumber} ) {
			$where .= " AND b_number LIKE '" . $c->req->params->{bnumber} . "\%'";
		}
	}

	my %check_tbl;
	foreach ( @{ $self->dbh->selectall_arrayref('SHOW TABLES') } ) {
		next unless $_->[0] =~ /^(?:acme|strom|ewsd|voip|axe).+(?:\d{4}_\d\d_\d\d)/;
		$check_tbl{ $_->[0] } = 1;
	}


	my $writer = $self->report_writer;
	$writer->init();

	my $t_loc = $c->config->{trace_location};
	my $t_ext = 'pcap';
	my $trace_cond = '';
	if ( $self->format eq 'html' ) {
		$trace_cond = qq{ CASE WHEN trace_id > 0 THEN }
				   . qq{ CONCAT('<a href="$t_loc/', Acme_Session_Egress_CallId_4, }
				   . qq{ '.$t_ext">загрузить</a>') }
				   . qq{ ELSE '' END, };
	}

	my $success = 0;
	foreach my $t_type ( keys %tables ) {
		my $select;
		if ( $c->req->params->{viewtype} eq 'full' ) {
			$select = 'SELECT * FROM ';
		}
		else {
			if ( $t_type =~ /^acme/ ) {
				$select = 'SELECT ' . ( $self->format eq 'html' ? $trace_cond : '')
						. ' protocol_type, switch, NAS_IP_Address, time_start, time_end, '
						. ' trunk_in, trunk_out, a_number, b_number, duration_call, duration_answer,'
						. ' Acme_Disconnect_Initiator_61, Cause, b_mask'
						. ' FROM '; 

			}
			else {
				$select = 'SELECT  protocol_type, switch, time_start, time_end, trunk_in, trunk_out, '
						. ' a_number, b_number, duration_call, duration_answer,'
						. ' Cause, b_mask'
						. ' FROM '; 
			}
		}
		$c->user_log->debug($select . ' table  WHERE ' 
			. qq{ time_end >= '$start_time' AND time_end <= '$end_time' } . $where, 'dbg search');

		foreach my $tbl ( @{ $tables{$t_type} } ) {
			next unless $check_tbl{$tbl};
			my $header = $self->get_header( $tbl ) if $tables{$t_type};
		
			my $query .= $select . $tbl . ' WHERE ' . ' time_end >= ? AND time_end <= ? ' . $where;

			push @{ $c->stash->{sql} }, $query;
			$c->user_log->debug($query, 'dbg search');

			my $sth = $self->dbh->prepare($query);
			$sth->execute($start_time, $end_time);

			$writer->add_page($tbl, $header);

			while ( my $row = $sth->fetch() ) {
				$success = 1;	
				$writer->add_row($row);
			}

			$writer->end_page();
		}
	}

	$writer->finish();

	$self->success($success);
 	if ( $self->success ) {
		$self->_set_data( $writer->result() );
		$self->result_rows( scalar( @{ $writer->result } ) - 1 )
			if ref $writer->result eq 'ARRAY';
	}

}



sub get_tables {
	my ($self, $d1, $d2, $table_names) = @_;

#	my $dbh = $c->model('DBIC')->schema->storage->dbh;

#	my @tables = qw(ewsd strom axe acme voip);
	my @tables = @$table_names;

	$Class::Date::DATE_FORMAT = '%Y-%m-%d';

	$d1 = date $d1;
	$d2 = date $d2;

	my @dates = ( $d1->string );
	while ( $d1->string ne $d2->string ) {
		$d1 += '1D';
		push @dates, $d1->string;
	}
	
	my %tables;
	foreach my $t ( @tables ) {
		foreach my $d (@dates) {
			$d =~ s/-/_/g;
			push @{ $tables{$t} }, $t . '_' . $d;
		}
	}
	return \%tables;
	
}

sub get_html {
	my ($self) = @_;
	if ( $self->success ) {
		my $table = HTML::Table->new(
			-data => $self->data,
			-class => 'search_table'
		);
		$table->setRowStyle(1, 'background: #efffef; font-weight: bolder');
		$table->setBorder(1);
		$table->setCellSpacing(0);

		return $table->getTable();
	}

	return '';
}

__PACKAGE__->meta->make_immutable;


package Search::Writer;

sub new {
	my $class = shift;
	my $self = ref $_[0] eq 'HASH' ? $_[0] : { @_ };
	return bless $self, $class;
}

sub init {}
sub add_page {}
sub add_row {}
sub end_page {}
sub finish {}
sub result { return shift->{result} }

# ------------- HTML --------------
package Search::Writer::HTML;

our @ISA = qw(Search::Writer);

sub add_page {
	my ($self, undef, $header) = @_;
	$self->{res} = [];
	$self->{row} = 0;
	if ( $header ) {
		$self->add_row($header);
	}
}

sub add_row { 
	my ($self, $row) = @_;

	push @{ $self->{res} }, [ $self->{row}, @$row ];
	$self->{row}++;
}

sub end_page {
	my ($self, $header) = @_;
	if ( @{ $self->{res} } ) {
		$self->{res}->[0]->[0] = 'N';
		push @{ $self->{result} }, @{ $self->{res} };
	}
}

package Search::Writer::Excel;

our @ISA = qw(Search::Writer);

sub init {
	my ($self) = @_;
	$self->{workbook}  = Spreadsheet::WriteExcel->new( $self->{file} );
}

sub add_page {
	my ($self, $name, $header) = @_;
	$self->{worksheet} = $self->{workbook}->add_worksheet($name);
	$self->{row} = 0;
	$self->{column} = 0;

	if ( $header ) {
		$self->add_row($header);
	}
}

sub add_row {
	my ($self, $data) = @_;

	$self->{column} = 0;
	
	if ( scalar( @$data ) == 13 ) {
		$data->[11] =~ s/^0$//;
	}
	else {
		$data->[10] =~ s/^0$//;
	}

	foreach ( @$data ) {
		$self->{worksheet}->write( $self->{row}, $self->{column}, $_ );
		$self->{column}++;
	}

	$self->{row}++;
}

sub finish {
	my ($self) = @_;

	my $format = $self->{workbook}->add_format(
		bold => 1,
		color => 'yellow',
		bg_color => 'gray',
		valign => 'vcenter',
		border => 1,
	);
	$self->{worksheet}->set_row(0, 23, $format, 0, 0, 1);
	$self->{workbook}->close();
}

package Search::Writer::CSV;

our @ISA = qw(Search::Writer);

use IO::File;

sub init {
	my ($self) = @_;
	my $fh = IO::File->new( $self->{file}, 'w' ) || die $!;
	$fh->binmode(':utf8');
	$self->{fh} = $fh;
}

sub add_page {
	my ($self, undef, $header) = @_;
	if ($header) {
		$self->add_row( $header );
	}
}

sub add_row {
	my ($self, $row) = @_;
	my $fh = $self->{fh};
	print $fh join(",", map { qq{"$_"} } @$row), "\n";
}

sub finish {
	my ($self) = @_;
	$self->{fh}->close();
}

1;
